package DBLayer;

import ModelLayer.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DBMaterial implements IFDBMaterial
{

    private Connection con;

    // Constructor
    public DBMaterial()
    {
        con = DbConnection1.getInstance().getDBcon();
    }
    
    //get a list of all materials
    @Override
    public ArrayList<Material> getAllMaterials(boolean retrieveAssociation)
    {
        return miscWhere("", retrieveAssociation);
    }
    
    //Find a material by id
    @Override
    public Material findMaterialId(String idMaterial, boolean retrieveAssociation)
    {
    	String wClause = "  idMaterial = '" + idMaterial + "'";
        return singleWhere(wClause, retrieveAssociation);
    }

    @Override
    public int insertMaterial(Material mat) throws Exception
    {  //call to get the next id
        int nextId = GetMax.getMaxId("Select max(idMaterial) from material");
        nextId = nextId + 1;
        System.out.println("next IdMaterial = " +  nextId);

        int rc = -1;
        String query = "INSERT INTO material(idMaterial, type, price, shine, colour, amount) VALUES('"+
                nextId  + "','"  +
                mat.getType()  + "','"  +
                mat.getPrice() + "','" +
                mat.getShine() + "','" +
                mat.getColour() + "','" +
                mat.getAmount() + "')'" ;

        System.out.println("insert : " + query);
        try
        { // insert new Material
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }//end try
        catch(SQLException ex)
        {
            System.out.println("The Material wasn't created");
            throw new Exception ("Material is not inserted correctly");
        }
        return(rc);
    }

    @Override
    public int updateMaterial(Material mat)
    {
        Material matObj  = mat;
        int rc=-1;

        String query = "UPDATE employee SET " +
                "type ='" + matObj.getType() + "', " +
                "price ='"+ matObj.getPrice() + "', " +
                "shine ='"+ matObj.getShine() + "', " +
                "colour ='"+ matObj.getColour() + "' " +
                "amount = '"+ matObj.getAmount() + "' " +
                " WHERE idMaterial = '" + matObj.getIdMaterial() +"'";
        System.out.println("Update query:" + query);
        try
        { // update material
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);

            stmt.close();
        }//slut try
        catch(Exception ex)
        {
            System.out.println("Update exception in material db: "+ex);
        }
        return(rc);
    }

    //Delete a material by id
    @Override
    public int deleteMaterial(String idMaterial)
	{
    	int rc=-1;
    	
	  	String query="DELETE FROM material WHERE idMaterial = '" +
	  	idMaterial + "'";
	  	System.out.println(query);
	  	try
	  	{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
	  	catch(Exception ex)
	  	{
	 	  	System.out.println("Delete exception in material db: "+ex);
	  	}
		return(rc);
	}

    
    private ArrayList<Material> miscWhere(String wClause, boolean retrieveAssociation)
	{
    	ResultSet results;
	    ArrayList<Material> list = new ArrayList<Material>();	
		
	    String query =  buildQuery(wClause);
  
        try
        { // read the material from the database
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
        	
        	while( results.next() )
        	{
        		Material matObj = new Material();
        		matObj = buildMaterial(results);	
                list.add(matObj);	
        	}//end while
        	stmt.close();     
        
        }//slut try	
	 	
        catch(Exception e)
        {
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
	//singleWhere is used when we only select one material
	private Material singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Material matObj = new Material();
                
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try
		{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			matObj = buildMaterial(results);
                stmt.close();
	 		}
            else
            { //no Material was found
            	matObj = null;
            }
		}//end try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return matObj;
	}
    
    private String buildQuery(String wClause)
    {
        // This string query needs to be revised.Query exception -
        // select: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'idMaterial'.
        // Spørgsmålet er om databasen er opdateret.
        String query = "Select idMaterial, type, price, shine, colour, amount";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        return query;
    }
    
    // method to build an Offer object
    private Material buildMaterial(ResultSet results)
    {
        Material matObj = new Material();
        try
        {
        	matObj.setIdMaterial(results.getString("idMaterial"));
            matObj.setType(results.getString("type"));
            matObj.setPrice(results.getDouble("price"));
            matObj.setShine(results.getInt("shine"));
            matObj.setColour(results.getString("colour"));
            matObj.setAmount(results.getDouble("amount"));
        }
        catch (Exception e)
        {
            System.out.println("error in building the offer object");
        }
        return matObj;
    }
}
